<?php
/**
 * DBShop 电子商务系统
 *
 * ==========================================================================
 * @link      https://www.dbshop.net/
 * @copyright 北京珑大钜商科技有限公司，并保留所有权利。
 * @license   https://www.dbshop.net/license.html License
 * ==========================================================================
 *
 * @author    静静的风 <baron@loongdom.cn>
 *
 */

namespace User\Repository;

use Admin\Data\Common;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use Sales\Entity\Order;
use User\Entity\User;
use User\Entity\UserGroup;
use User\Repository\QuerySearch\UserSearch;
use function Doctrine\ORM\QueryBuilder;

class UserRepository extends EntityRepository
{
    /**
     * 后台会员列表
     * @param array $search
     * @return \Doctrine\ORM\Query
     */
    public function findAllUser($search = [])
    {
        $query = $this->getEntityManager()->createQueryBuilder()
            ->select('u', 'g')
            ->from(User::class, 'u')
            ->join('u.oneUserGroup', 'g')
            ->orderBy('u.userId', 'DESC');

        $query = UserSearch::querySearchData($search, $query);

        return $query->getQuery();
    }

    /**
     * 客户数量统计
     * @param string $startTime
     * @param string $endTime
     * @return int|mixed|string
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findUserCount($startTime = '', $endTime = '')
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('COUNT(u.userId)')->from(User::class, 'u');
        if (!empty($startTime)) $query->where($query->expr()->gte('u.userAddTime', ':startTime'))->setParameter('startTime', $startTime);
        if (!empty($endTime))   $query->andWhere($query->expr()->lte('u.userAddTime', ':endTime'))->setParameter('endTime', $endTime);

        return $query->getQuery()->getSingleScalarResult();
    }

    /**
     * 根据时间分组统计客户数量
     * @param $startTime
     * @param $endTime
     * @param string $dateType
     * @return mixed[]
     * @throws \Doctrine\DBAL\DBALException
     */
    public function findUserDateCount($startTime, $endTime, $dateType = '%Y-%m-%d')
    {
        $query  = $this->getEntityManager()->getConnection();
        $sql    = "SELECT COUNT(u.user_id) AS userNum, FROM_UNIXTIME(u.user_add_time, '".$dateType."') AS userDate FROM dbshop_user AS u";
        $sql    .= " WHERE u.user_add_time >= ".$startTime;
        $sql    .= " AND u.user_add_time <= ".$endTime;
        $sql    .= " GROUP BY userDate";

        $stmt   = $query->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * 客户统计数量排行
     * @param $startTime
     * @param $endTime
     * @param int $userGroupId
     * @return \Doctrine\ORM\Query
     */
    public function findUserRanking($startTime, $endTime, $userGroupId = 0)
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('u', 'g')
            ->addSelect("(SELECT SUM(o.baseOrderAmount) FROM ".Order::class." AS o WHERE o.userId=u.userId AND ((o.paymentType = 'OnlinePay' AND o.orderStatus >= ".Common::orderStatusCode('WAIT_GOODS_ARRIVE').") OR (o.paymentType = 'CashOnDelivery' AND o.orderStatus >= ".Common::orderStatusCode('GOODS_RECEIVED').")) AND o.orderAddTime >= ".$startTime." AND o.orderAddTime <= ".$endTime.") AS orderTotal")
            ->addSelect("(SELECT COUNT(ot.orderId) FROM ".Order::class." AS ot WHERE ot.userId=u.userId AND ((ot.paymentType = 'OnlinePay' AND ot.orderStatus >= ".Common::orderStatusCode('WAIT_GOODS_ARRIVE').") OR (ot.paymentType = 'CashOnDelivery' AND ot.orderStatus >= ".Common::orderStatusCode('GOODS_RECEIVED').")) AND ot.orderAddTime >= ".$startTime." AND ot.orderAddTime <= ".$endTime.") AS orderNum")
            ->from(User::class, 'u')
            ->join('u.oneUserGroup', 'g')
            ->orderBy('orderTotal', 'DESC');

        if ($userGroupId > 0) $query->where('u.userGroupId = :userGroupId')->setParameter('userGroupId', $userGroupId);

        return $query->getQuery();
    }

    /*==============================================下面为前台调用==============================================*/
    /**
     * 会员登录
     * @param $loginAccount
     * @return int|mixed|string
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findUserLogin($loginAccount)
    {
        $query = $this->getEntityManager()->createQueryBuilder()
            ->select('u', 'g')
            ->from(User::class, 'u')
            ->join('u.oneUserGroup', 'g')
            ->where('u.userName = :userName')->setParameter('userName', $loginAccount);

        //检查是否开启了邮箱和手机登录
        $loginItem = Common::configValue('login_item', 'customer');
        if (!empty($loginItem)) {
            if (in_array('email', $loginItem)) $query->orWhere('u.userEmail = :userEmail')->setParameter('userEmail', $loginAccount);
            if (in_array('phone', $loginItem)) $query->orWhere('u.userPhone = :userPhone')->setParameter('userPhone', $loginAccount);
        }

        return $query->getQuery()->getOneOrNullResult();
    }
}